package com.baide.sanguanheyi.luodao;

import com.baide.sanguanheyi.plc.PlcConfig;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 * 洛道系统dao
 */
@Repository
public class Ldao {

    private Logger logger = LoggerFactory.getLogger(Ldao.class);
    @Autowired
    PlcConfig config;
    @Autowired
    @Qualifier("otherJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 根据barcode查询数据
     *
     * @param barcode
     * @return
     */
    public Map<String, String> selectByBarcode(String barcode) {
        Map<String, String> resultMap = null;
        try {
            String sql = "select x.f_sort_port(BILL_NO,'" + config.getXt_no() + "', CUST_ORDER,CUST_ORDER2,V_TYPE,V_SOURCE" +
                    ",I_E_FLAG,TAX_FLAG,RETURNSTATUS, RETURNINFO) 控制指令,t.cust_order 状态,t.cust_order2 原始状态,t.returnstatus 海关状态,t.returninfo,t.* " +
                    "from x.V_X_EDI_FREIGHTINFO_SCAN_BD  t where t.BILL_NO=? order by t.id";
            resultMap = jdbcTemplate.queryForObject(sql, new RowMapper<Map<String, String>>() {
                @Override
                public Map<String, String> mapRow(ResultSet resultSet, int rowNum) throws SQLException {
                    Map<String, String> resultMap = null;
                    if (resultSet != null) {
                        resultMap = new HashMap<String, String>();
                        resultMap.put("bill_no", resultSet.getString("BILL_NO"));
                        resultMap.put("cust_order", resultSet.getString("CUST_ORDER"));
                        resultMap.put("cust_order2", resultSet.getString("CUST_ORDER2"));
                        resultMap.put("v_type", resultSet.getString("V_TYPE"));
                        resultMap.put("v_source", resultSet.getString("V_SOURCE"));
                        resultMap.put("i_e_flag", resultSet.getString("I_E_FLAG"));
                        resultMap.put("tax_flag", resultSet.getString("TAX_FLAG"));
                        resultMap.put("return_status", resultSet.getString("RETURNSTATUS"));
                        resultMap.put("return_info", resultSet.getString("RETURNINFO"));
                        resultMap.put("trade_code", resultSet.getString("TRADE_CODE"));
                        resultMap.put("pack_no", resultSet.getString("PACK_NO"));
                        resultMap.put("main_g_name", resultSet.getString("MAIN_G_NAME"));
                        resultMap.put("voyage_no", resultSet.getString("VOYAGE_NO"));
                        resultMap.put("SEND_NAME", resultSet.getString("SEND_NAME"));
                        resultMap.put("SENDER_ADDR", resultSet.getString("SENDER_ADDR"));
                        resultMap.put("SENDER_TEL", resultSet.getString("SENDER_TEL"));
                        resultMap.put("OWNER_NAME", resultSet.getString("OWNER_NAME"));
                        resultMap.put("OWNER_ADDR", resultSet.getString("OWNER_ADDR"));
                        resultMap.put("OWNER_TEL", resultSet.getString("OWNER_TEL"));
                    }
                    return resultMap;
                }
            }, barcode);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }

        return resultMap;
    }

    public void insertPic(Map<String, String> paramMap) {
        String sql = "insert into x.tb_pic(ID,BILL_NO,V_PATH,D_PASS_DATETIME,V_NOTE,N_SEQ,JQBH) values(?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, getID("select x.seq_pic.nextval from dual"));
                ps.setString(2, paramMap.get("bill_no"));
                ps.setString(3, paramMap.get("v_path"));
                ps.setDate(4, getOracleSysDate("select sysDate from dual"));
                ps.setString(5, "百德");
                ps.setInt(6, 1);
                ps.setString(7, paramMap.get("xt_no"));
            }
        });
    }


    public void insertHgOrgjOrder(Map<String, String> paramMap) {
        String sql = "insert into x.io_x_hgorgj_order(ID, KJDH, HG, HGSJ, HGR, JQBH, TRADE_CODE, RESULTMASK, JUDGECATEGORY) values(?,?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, getID("select x.seq_x_hgorgj_order.nextval from dual"));
                ps.setString(2, paramMap.get("bill_no"));
                ps.setString(3, paramMap.get("hg"));
                ps.setDate(4, getOracleSysDate("select sysDate from dual"));
                ps.setString(5, paramMap.get("optUser"));
                ps.setString(6, paramMap.get("xt_no"));
                ps.setString(7, paramMap.get("trade_code"));
                ps.setString(8, paramMap.get("return_mask"));
                ps.setString(9, paramMap.get("judge_category"));
            }
        });
    }


    public void insertXWeight(Map<String, String> paramMap) {
        String sql = "insert into x.io_x_weight(ID, KJDH, LSR, CZSJ, ZL, JQBH, HGBZ,TRADE_CODE) values(?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                //ps.setInt(1, "select x.seq_pic.nextval from dual");
                ps.setInt(1, getID("select x.seq_x_weight.nextval from dual"));
                ps.setString(2, paramMap.get("bill_no"));
                ps.setString(3, paramMap.get("user"));
                ps.setDate(4, getOracleSysDate("select sysDate from dual"));
                ps.setString(6, paramMap.get("xt_no"));
                ps.setDouble(5, Integer.parseInt(paramMap.get("zl")) / 1000);
//                ps.setInt(7, Integer.parseInt(paramMap.get("hgbz")));
                ps.setInt(7, 1);
                ps.setString(8, paramMap.get("trade_code"));

            }
        });
    }

    private Date getOracleSysDate(String sql) {
        return jdbcTemplate.queryForObject(sql, new RowMapper<Date>() {
            @Override
            public Date mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getDate("SYSDATE");
            }
        });
    }

    private int getID(String sql) {
        return jdbcTemplate.queryForObject(sql, new RowMapper<Integer>() {
            @Override
            public Integer mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getInt("NEXTVAL");
            }
        });
    }
}
